package com.um.psystem.service.sopService.impl;

import cn.hutool.core.util.StrUtil;
import com.um.psystem.entity.ExcelHeader;
import com.um.psystem.mapper.platform.PublicMapper;
import com.um.psystem.service.sopService.ISOPDownloadService;
import com.um.psystem.utils.EasyExcelUtils;
import com.um.psystem.utils.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Auther: AiLen2E
 * @Date: 2021/9/9 20:08
 * @version: 1.0
 */
@Service
public class SOPDownloadService implements ISOPDownloadService {

    @Autowired
    PublicMapper publicMapper;

    @Override
    public List<Map<String, Object>> getSopRecordListByBomNum(Map map) {

        String sqltmp = "";
        String bomMoudleNum = "";
        Map paramsMap = new HashMap();
        Integer page = Integer.parseInt((map.get("page") != null ? map.get("page").toString() : "0"));
        Integer rows = Integer.parseInt((map.get("rows") != null ? map.get("rows").toString() : "0"));

        if (StrUtil.isNotBlank(map.get("bomMoudleNum") != null ? map.get("bomMoudleNum").toString() : null)) {
            if (map.containsKey("queryType") && StrUtil.isNotBlank(map.get("queryType").toString())) {
                String queryType = map.get("queryType").toString();
                switch (queryType)
                {
                    case "0":sqltmp += "AND a.FBOMNumber =#{bomMoudleNum}";break;
                    case "1":sqltmp += "AND a.FBOMNumber IN " +
                            "(SELECT q.FBOMNumber FROM ws_k3_bom q " +
                            "JOIN ws_k3_bomdetail a ON q.FInterID=a.FInterID " +
                            "JOIN ws_k3_icitem b ON a.FItemID=b.FItemID " +
                            "WHERE q.FName='产成品' AND b.FErpclsName IN ( '虚拟件', '自制' ) " +
                            "AND b.FNumberShort=#{bomMoudleNum})";break;
                }
            }
            bomMoudleNum = map.get("bomMoudleNum").toString();
            paramsMap.put("bomMoudleNum", bomMoudleNum);
        }

        String sql = "SELECT COUNT(*) as totals FROM ws_k3_bom q " +
                "JOIN ws_k3_bomdetail a ON q.FInterID=a.FInterID " +
                "JOIN ws_k3_icitem b ON a.FItemID=b.FItemID " +
                "LEFT JOIN (SELECT * FROM ws_eng_sop_bom_module_dtl tempd WHERE tempd.version = (SELECT MAX(tempd2.version) FROM ws_eng_sop_bom_module_dtl tempd2 " +
                "                 WHERE tempd2.bom_module_num=tempd.bom_module_num)) dtl ON dtl.bom_module_num=b.FNumberShort " +
                "LEFT JOIN ws_eng_sop_bom_module_main mn ON mn.id=dtl.main_id " +
                "LEFT JOIN ws_eng_sop_production_process pcs ON dtl.production_process_id=pcs.id " +
                "WHERE q.FName='产成品' AND b.FErpclsName IN ( '虚拟件', '自制' ) "+sqltmp;

        paramsMap.put("sqlStr", sql);
        int totals = 0;
        List<Map<String, Object>> list_count = publicMapper.getPublicItems(paramsMap);
        if (list_count.size() > 0) {
            totals = Integer.parseInt(list_count.get(0).get("totals").toString());
        } else {
            System.out.println("****************:" + totals);
        }

        sql = "SELECT q.FBOMNumber,q.FModel,q.FCreateName,b.FNumber,b.FName,b.FModel as FModel1,dtl.bom_module_num," +
                "dtl.version,pcs.production_process,dtl.controlled_num,dtl.download_url,dtl.date,dtl.operator,"+totals+" as totals FROM ws_k3_bom q " +
                "JOIN ws_k3_bomdetail a ON q.FInterID=a.FInterID " +
                "JOIN ws_k3_icitem b ON a.FItemID=b.FItemID " +
                "LEFT JOIN (SELECT * FROM ws_eng_sop_bom_module_dtl tempd WHERE tempd.version = (SELECT MAX(tempd2.version) FROM ws_eng_sop_bom_module_dtl tempd2\n" +
                "                 WHERE tempd2.bom_module_num=tempd.bom_module_num)) dtl ON dtl.bom_module_num=b.FNumberShort " +
                "LEFT JOIN ws_eng_sop_bom_module_main mn ON mn.id=dtl.main_id " +
                "LEFT JOIN ws_eng_sop_production_process pcs ON dtl.production_process_id=pcs.id " +
                "WHERE q.FName='产成品' AND b.FErpclsName IN ( '虚拟件', '自制' ) " + sqltmp;

            if (page != 0 && rows != 0) {
                sql += " limit #{startIndex},#{rows} ";
                paramsMap.put("startIndex", (page - 1) * rows);
                paramsMap.put("rows", rows);
            }
        paramsMap.put("sqlStr", sql);
        return publicMapper.getPublicItems(paramsMap);
    }

    @Override
    public List<Map<String, Object>> getModelRecordListByModelName(Map map) {
        String sqltmp = "";
        String modelName = "";
        Map paramsMap = new HashMap();
        Integer page = Integer.parseInt((map.get("page") != null ? map.get("page").toString() : "0"));
        Integer rows = Integer.parseInt((map.get("rows") != null ? map.get("rows").toString() : "0"));

        if (StrUtil.isNotBlank(map.get("modelName") != null ? map.get("modelName").toString() : null)) {
            modelName = map.get("modelName").toString();
            paramsMap.put("modelName", modelName);
        }
        String sql = "select count(*) totals from ws_eng_sop_machine_model_dtl a " +
                "join ws_eng_sop_production_process b on a.production_process_id=b.id " +
                "where a.machine_model=#{modelName} order by a.version desc " + sqltmp;

        paramsMap.put("sqlStr", sql);
        int totals = 0;
        List<Map<String, Object>> list_count = publicMapper.getPublicItems(paramsMap);
        if (list_count.size() > 0) {
            totals = Integer.parseInt(list_count.get(0).get("totals").toString());
        } else {
            System.out.println("****************:" + totals);
        }

        sql = "select a.machine_model,a.version,a.controlled_num,a.operator," +
                "a.date,a.download_url,a.remark,b.production_process,'"+ totals +"' as totals " +
                "from ws_eng_sop_machine_model_dtl a " +
                "join ws_eng_sop_production_process b on a.production_process_id=b.id " +
                "where a.machine_model=#{modelName} order by a.version desc " + sqltmp;

        if (page != 0 && rows != 0) {
            sql += " limit #{startIndex},#{rows} ";
            paramsMap.put("startIndex", (page - 1) * rows);
            paramsMap.put("rows", rows);
        }
        paramsMap.put("sqlStr", sql);
        return publicMapper.getPublicItems(paramsMap);
    }

    @Override
    public void exportSOPRelationshipData(HttpServletResponse response, Map map) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = null;
        try {
            fileName = URLEncoder.encode("全部SOP关系表", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        List<ExcelHeader> headerList = new ArrayList<>();
        headerList.add(new ExcelHeader("FBOMNumber", "产成品编码"));
        headerList.add(new ExcelHeader("FModel", "产成品规格型号"));
        headerList.add(new ExcelHeader("FCreateName", "BOM创建者"));

        headerList.add(new ExcelHeader("FNumber", "半成品编码"));
        headerList.add(new ExcelHeader("FName", "物料名称"));
        headerList.add(new ExcelHeader("FModel1", "半成品规格型号"));
        headerList.add(new ExcelHeader("bom_module_num", "SOP所属BOM模块短编码"));
        headerList.add(new ExcelHeader("version", "SOP版本(最新)"));
        headerList.add(new ExcelHeader("controlled_num", "SOP受控编码"));

        ExcelHeader dateBackHeader = new ExcelHeader("date", "SOP上传日期");
        dateBackHeader.setDataType(ExcelHeader.DATE);
        dateBackHeader.setFormat("yyyy-MM-dd HH:mm:ss");
        headerList.add(dateBackHeader);

        headerList.add(new ExcelHeader("operator", "操作人"));
        headerList.add(new ExcelHeader("production_process", "工段"));
        headerList.add(new ExcelHeader("download_url", "SOP下载路径"));

        List<Map<String, Object>> dataList = getSopRecordListByBomNum(map);
        OutputStream excelOutputStream = EasyExcelUtils.exportDataToExcel(headerList, dataList);
        try {
            response.getOutputStream().write(((ByteArrayOutputStream) excelOutputStream).toByteArray());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
